﻿using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace HmLab.Func1;

//https://learn.microsoft.com/en-us/visualstudio/vsto/office-primary-interop-assemblies?view=vs-2022
internal class LbFillResultInput {

  /// <summary>
  /// <see cref="Haimen.Func1.ResultInputFiller"/>
  /// </summary>
  public void Run() {
    System.Console.WriteLine("-------LbFillResultInputLbFillResultInputLbFillResultInput");
    string[] groupList = ["W本周", "W上周", "W去年周"];

    string resultTpl = @"E:\work_etc\nina\2023haimen2\workshop_cslab\输Q出\周数据分析.xlsx";
    string resultPath = resultTpl.Replace(".xlsx", "gen.xlsx");
    File.Copy(resultTpl, resultPath, overwrite: true);

    string mergeOut = @"D:\work\personal\nina\2023haimen2\workshop_cslab\输Q出\数据源_240119.xlsx";
    Application excelApp = new() {
      Visible = true,
      ScreenUpdating = false,
    };

    var OpenBook = (string path) => {
      Debug.Assert(File.Exists(path));
      return excelApp.Workbooks.Open(path);
    };

    Workbook resultBook = OpenBook(resultPath);
    Workbook mergeBook = OpenBook(mergeOut);

    foreach (string sheetName in groupList) {
      Worksheet fromSheet = mergeBook.Sheets[sheetName];
      Worksheet toSheet = resultBook.Sheets[sheetName];
      CopySheet(fromSheet, toSheet, "A1");
    }

    mergeBook.Activate();
    excelApp.CutCopyMode = 0;
    mergeBook.Close();

    resultBook.Save();
    resultBook.Close();
    excelApp.Quit();
  }

  void CopySheet(Worksheet fromSheet, Worksheet toSheet, string toCell) {
    fromSheet.Activate();

    Range lastCell = fromSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
    Range all = fromSheet.Range[fromSheet.Cells[1, 1], lastCell];
    //Range all = fromSheet.Range["A1:H3"];
    int rowCount = all.Rows.Count;
    //System.Console.WriteLine("------------- " + rowCount);
    all.Select();
    all.Copy();

    Range target = toSheet.Range[toCell];
    toSheet.Activate();
    target.Select();
    toSheet.Paste();

    // 删除范围外多余的行
    DeleteBelow(toSheet, target.Row + rowCount);
  }

  void DeleteBelow(Worksheet sheet, int delStart) {
    int delEnd = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
    if (delStart > delEnd) {
      return;
    }

    //System.Console.WriteLine($"***---------- {delStart} {delEnd}");
    Range delRange = sheet.Rows[$"{delStart}:{delEnd}"];
    delRange.Select();
    delRange.Delete();

    sheet.Cells[delStart, 1].Select();
  }
}
